/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dnbW.dbW.sucxentdbW;

import dnbW.dbW.Document;
import dnbW.entityW.IPath;
import dnbW.entityW.Path;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.SortedSet;
import java.util.TreeSet;

/**
 * This object is in charge of managing/loading/storing of the cardinality
 * information of a data source
 * @author truongbaquan
 */
public class CardinalityManager {
    /**
     * The data source that this object is working on
     */
    private SucxentDataSource dataSource;

    /**
     * List of internal paths (including leaf paths without values)
     */
    private List<Path> internalPaths;

    public Collection<Path> getInternalPaths() {
        if (internalPaths == null) this.loadInternalPaths();
        return internalPaths;
    }

    /**
     * List of paths that are child nodes of a "+" edge
     */
    private HashSet<Path> plusPaths = new HashSet<Path>();

    public Collection<Path> getPlusPaths() {
        return plusPaths;
    }

    // <editor-fold defaultstate="collapsed" desc="Constructors">
    /**
     * Constructs the cardinality manager on the given data source
     * @param dataSource
     */
    public CardinalityManager(SucxentDataSource dataSource) {
        this.dataSource = dataSource;
        this.loadCardinalityInformation();
    }
    // </editor-fold>

    private void loadCardinalityInformation() {
        try {
            Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
            statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

            plusPaths = new HashSet<Path>();
            ResultSet sqlResult = statement.executeQuery("SELECT PathExp FROM " + this.dataSource.getName() + "_CardinalityInfo");
            while (sqlResult.next()) {
                Path plusPath = Path.buildPathFromSqlPathExp(dataSource, sqlResult.getString("PathExp"));
                this.plusPaths.add(plusPath);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Process the cardinality of all paths in the document by identifying whether the relationship
     * between a parent type and a descendant type is 1-1 or 1-n
     * @param documents    The list of documents that we need to check
     * @throws Exception
     */
    public void processCardinalityInformation(Collection<Document> documents) throws Exception {
        System.out.println("* Identifying Cardinality *");
        this.loadInternalPaths();
        Collection<CardinalityPath> cPaths = findCardinality(documents);
        for (Document document : documents) {
            System.out.println("Document " + document.getDocId());
        }
        for (CardinalityPath cPath : cPaths) {
            System.out.println("\t" + cPath.path.toPathExp());
        }
        storeCardinality(cPaths);
        System.out.println("* Finish identifying cardinality *");
    }

    private Collection<CardinalityPath> findCardinality(Collection<Document> documents) {
        try {
            Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement();
            statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

            List<CardinalityPath> cPaths = new ArrayList<CardinalityPath>();
            for (Path path : internalPaths) {
                String thisPathIds = SucxentQueryTranslator.produceIdLeafList(path);

                // Check whether the relationship between the parent path and the current path is 1-to-1 or 1-to-multiple
                if (path.getLevel() < 0) {
                }
                else if (path.getLevel() == 1) {
                    if (!SucxentQueryTranslator.isSingleDoc(dataSource)) {
                        CardinalityPath cPath = new CardinalityPath(path, true);
                        cPaths.add(cPath);
                    }
                }
                else if (path.getLevel() == 2) {
                    for (Document document : documents) {
                        String sql = "SELECT COUNT(*) AS Counter " +
                                "FROM " + document.getDataSource().getName() + "_PATHVALUE V " +
                                "WHERE V.DocId = " + document.getDocId() +
                                "AND V.PathId IN " + thisPathIds + " " +
                                "AND V.BranchOrder < " + path.getLevel();
                        ResultSet result = statement.executeQuery(sql);
                        int X = 0;
                        if (result.next())
                            X = result.getInt("Counter");
                        if (X > 1) {
                            CardinalityPath cPath = new CardinalityPath(path, true);
                            cPaths.add(cPath);
                            break; // out of document for-loop
                        }
                    }
                } else {
                    for (Document document : documents) {
                        // Select the number of nodes with current path underlying each node with parent path and count them.
                        long ancestorValue = document.getAncestorValue(path.getLevel()-2);
                        String ancestorValueString = "(cast (" + ancestorValue + " as bigint))";
                        String sql = "SELECT  TOP 1  V1.AncestorDeweyGroup/" + ancestorValueString + ", COUNT(*)\n" +
                                "FROM " + document.getDataSource().getName() + "_PathValue V1\n" +
                                "WHERE V1.DocId = " + document.getDocId() +
                                " AND V1.PathId IN " + thisPathIds +
                                " AND V1.BranchOrder < " + path.getLevel() +
                                " GROUP BY  V1.AncestorDeweyGroup/" + ancestorValueString +
                                " HAVING COUNT(*)>1 " +
                                " OPTION (force order, order group) ";
                        //System.out.println(sql);
                        int X = 0;
                        ResultSet result = statement.executeQuery(sql);
                        if (result.next()) {
                            X++;
                        }
                        if (X > 0) {
                            CardinalityPath cPath = new CardinalityPath(path, true);
                            cPaths.add(cPath);
                            break; // out of document for-loop
                        }
                    }
                }
            }
            statement.close();
            return cPaths;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }

    }

    /**
     * Store cardinality information of each paths to the table CardinalityInfo
     * @param dataSource
     * @param cPaths
     */
    private void storeCardinality(Collection<CardinalityPath> cPaths) {
        Statement statement;
        try {
            statement = this.dataSource.getDatabase().getDbConnection().createStatement();
            statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");

            // Insert unstored path cardinalities into table CardinalityInfo
            for (CardinalityPath cPath : cPaths) {
                if (cPath.isCardinality() && !plusPaths.contains(cPath.path)) {
                    this.plusPaths.add(cPath.getPath());
                    String sql = "INSERT INTO " + this.dataSource.getName() + "_CardinalityInfo VALUES (" +
                            " " + 1 + ", " +
                            "'" + cPath.getPath().getElementAtLevel(cPath.getPath().getLevel()) + "', " +
                            "'" + cPath.getPath().toSQLPathExp() + "') ";
                    statement.addBatch(sql);
                }
            }
            statement.executeBatch();
            statement.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Retrieve all internal paths in the data source
     * @return                  List of internal paths' information
     */
    private void loadInternalPaths() {
        SortedSet<String> internalPathExprSet = new TreeSet<String>();

        for (int pathId = 1; pathId <= this.dataSource.getLeafPathManager().getNumberOfPaths(); pathId++) {
            LeafPath leafPath = this.dataSource.getLeafPathManager().getPath(pathId);
            if (leafPath != null && !leafPath.isAttribute()) {
                // build all the ancestor internal paths of the leaf path
                StringBuilder ancestorPathBuilder = new StringBuilder();
                for (int level = 1; level <= leafPath.getLevel()-1; level++) { // length - 1 => we ignore the leaf label
                    ancestorPathBuilder.append("." + leafPath.getElementAtLevel(level) + "#");
                    internalPathExprSet.add(ancestorPathBuilder.toString()); // add the internal path (not added if exists)
                }
                if (this.dataSource.getLeafPathManager().isLeafPathNullValue(leafPath) && this.getNumberOfAttributes(leafPath) > 1) {
                    internalPathExprSet.add(leafPath.toSQLPathExp());
                }
            }  
        }

        // build the internal path list (since the set is sorted, the list will be sorted)
        if (internalPaths == null) internalPaths = new ArrayList<Path>();
        else internalPaths.clear();
        for (String internalPathExpr : internalPathExprSet) {
            Path path = Path.buildPathFromSqlPathExp(this.dataSource, internalPathExpr);
            internalPaths.add(path);
        }
    }

    private int getNumberOfAttributes(IPath path) {
        try {
            Statement statement = this.dataSource.getDatabase().getDbConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.executeUpdate("USE [" + this.dataSource.getDatabase().getDbName() + "];");
            String sql = "select count(*) from " + this.dataSource.getName() + "_Path where PathExp like '" + path.toSQLPathExp() + ".@%'";
            ResultSet sqlResult = statement.executeQuery(sql);
            sqlResult.next();
            int result = sqlResult.getInt(1);
            sqlResult.close();
            statement.close();
            return result;
        }
        catch (SQLException ex) {
            System.err.println(ex.getMessage());
            ex.printStackTrace();
            return 0;
        }
    }

    private class CardinalityPath {
        private Path path;

        public Path getPath() {
            return path;
        }

        private boolean cardinality;

        public boolean isCardinality() {
            return cardinality;
        }

        public CardinalityPath(Path path, boolean cardinality) {
            this.path = path;
            this.cardinality = cardinality;
        }
    }
}
